﻿Namespace Emeklilik.KurFarki
	Module Functions

		Function MaasDonemleriSource() As String
			Return "q@select distinct yil*100+ay as kod, ltrim(yil)+'/'+right('00'+ltrim(ay),2) as ad from yaslılık..kcmaasdetay order by 1 desc"
		End Function

		Function Bordro(ByVal Process As Token, ByVal Yil As Integer, ByVal Ay As Integer) As Recordset
			Q("select m.odeyerkod,oy.acik as odeyeradi,b.hturu, b.kararno, rtrim(b.adi)+' '+rtrim(b.soyadi) as adisoyadi,b.kimlik, m.fark-isnull(m.kesfark,0) as maas,")
			Q("case when m.odeyerkod < 20 then convert(varchar(99),m.cekno) else m.bankahno end as bankahno,")
			Q("case when m.odeyerkod < 20 then m.cekno else '' end as ymocekno, cekno")
			Q("from yaslılık..kcmaasdetay m inner join yaslılık..yasmaasbil b on m.hturu = b.hturu and m.kimlik = b.kimlik")
			Q("left join yaslılık..odemeyeri oy on m.odeyerkod = oy.odeyerkod")
			Q("where m.yil = :yil and m.ay = :ay order by m.odeyerkod,b.kimlik")
			Return Process.OpenSQL("*", Yil, Ay)
		End Function

		Function Hesap(ByVal Process As Token, ByVal Yil As Integer, ByVal Ay As Integer) As Recordset
			Process.DropTemporaryTables("temp")
			Dim Tarih As Date = ToDate(Yil, If(Ay = 13, 12, Ay), 1)
			Dim Kur As Decimal = Process.OpenSQL("select alis from yaslılık..kur where tarih between :t1 and :t2", Tarih, Tarih.AddMonths(1).AddDays(-1))(0)
			Q("select a.kimlik, rtrim(adi) + ' ' + rtrim(soyadi) as adi, a.hturu, borclu, katsayi as katsayi, cektutar, @kur as kur, b.odeyerkod, b.bankahno,")
			Q("derece = case when b.yderece = 0 then b.derece else b.yderece end, basamak = case when b.ybasamak = 0 then b.basamak else b.ybasamak end into #temp")
			Q("from yaslılık..kcmaas a left join yaslılık..yasmaasbil b on a.hturu = b.hturu and a.kimlik = b.kimlik where a.donduruldu <> 1")
			Q("and exists(select * from yaslılık..yasmaas where yil = @yil and ay = @ay and hturu = b.hturu and kimlik = b.kimlik)")
			Process.ExecSQL("*", Kur, Yil, Ay)
			Process.ExecSQL("alter table #temp add cekytl decimal(15,2)")
			Process.ExecSQL("alter table #temp add maas decimal(15,2)")
			Process.ExecSQL("alter table #temp add aldmaas decimal(15,2)")
			Process.ExecSQL("alter table #temp add kesmaas decimal(15,2)")
			Process.ExecSQL("alter table #temp add fark decimal(15,2)")
			Process.ExecSQL("alter table #temp add kesfark decimal(15,2)")
			Process.ExecSQL("alter table #temp add netfark decimal(15,2)")
			Process.ExecSQL("update #temp set cekytl = cektutar * kur")
			Process.ExecSQL("update #temp set maas = yaslılık.dbo.maas(:tarih,derece,basamak) * katsayi / 100000, aldmaas = b.maas,kesmaas = isnull(b.kesmaas,0) from #temp a inner join yaslılık..yasmaas b on b.yil = :yil and b.ay = :ay and a.kimlik = b.kimlik and a.hturu = b.hturu", Tarih, Yil, Ay)
			Process.ExecSQL("update #temp set fark = maas - (borclu+1) * aldmaas - cekytl")
			Process.ExecSQL("update #temp set fark = 0 where fark < 0")
			Process.ExecSQL("update #temp set kesfark = case when kesmaas = 0 then 0 else fark * 0.01 end from #temp")
			Return Process.OpenSQL("select * from #temp order by kimlik, hturu")
		End Function

		Function Kaydet(ByVal Process As Token, ByVal Yil As Integer, ByVal Ay As Integer) As Boolean
			Try
				Process.BeginTran()
				Process.ExecSQL("alter table #temp add cekno int")
				Dim Cek As Integer = Process.OpenSQL("select max(cekno) from muhasebe..yascek updlock")(0)
				Process.ExecSQL("delete from #temp where fark - kesfark = 0 or odeyerkod >= 900")
				Process.ExecSQL("delete from yaslılık..kcmaasdetay where yil = :yil and ay = :ay and ayeki = 0", Yil, Ay)
				Q("select adi, kimlik, fark - kesfark as tutar, odeyerkod, 0 as cekno into #cektemp from #temp where odeyerkod < 20 union all")
				Q("select b.acik, '', sum(fark - kesfark), b.odeyerkod, 0 from #temp a inner join yaslılık..odemeyeri b on a.odeyerkod = b.odeyerkod")
				Q("where	b.odeyerkod between 20 and 899 group by b.odeyerkod, b.acik order by odeyerkod, kimlik")
				Process.ExecSQL("*")
				Process.ExecSQL("alter table #cektemp add cekid int identity")
				Process.ExecSQL("select * into #cek from #cektemp")
				Process.ExecSQL("update #cek set cekno = cekid + :cek", Cek)
				Process.ExecSQL("update #temp set cekno = b.cekno from #temp a inner join #cek b on a.odeyerkod = b.odeyerkod and b.kimlik in ('',a.kimlik)")
				Q("insert into yaslılık..kcmaasdetay (kimlik, hturu, yil, ay, ayeki, maas, aldigimaas, kcmaas, kur, kcmaasytl, fark, islemtar, odeyerkod, bankahno, cekno, kesfark)")
				Q("select kimlik, hturu, :yil, :ay, 0, maas, aldmaas, cektutar, kur, cekytl, fark, getdate(), odeyerkod, bankahno, cekno, kesfark")
				Q("from #temp order by kimlik")
				Process.ExecSQL("*", Yil, Ay)
				Q("insert into muhasebe..yascek (cekkod,cekno,kime,kimlik,miktar,kaykul,islemtar,tarih,sube)")
				Q("select 'K', cekno, adi, kimlik, tutar, 'OTOM', getdate(), convert(varchar(8),getdate(),112), 1 from #cek")
				Process.ExecSQL("*")
				Q("insert into muhasebe..yascekdetay (cekno,hesapno,tutar)")
				Q("select cekno, case hturu when '2306' then '1220201' when '2316' then '1220202' when '2326' then '1220203' end, sum(fark - kesfark)")
				Q("from #temp group by cekno, case hturu when '2306' then '1220201' when '2316' then '1220202' when '2326' then '1220203' end")
				Process.ExecSQL("*")
				Process.CommitTran()
				Return True
			Catch ex As Exception
				Process.Rollback()
				Return False
			End Try
		End Function

		Function DonemSorgula(ByVal Token As Token, ByVal yil As Integer, ByVal ay As Integer) As Boolean
			Return Token.OpenSQL("select count(*) from yaslılık..kcmaasdetay where yil = @yil and ay = @ay", yil, ay)(0) > 0
		End Function

		Function MaasAra(ByVal Token As Token, ByVal kimlik As String) As Recordset
			Return Token.OpenSQL("select hturu as kod,hturu as ad from yaslılık..yasmaasbil where kimlik = @kimlik and hturu in ('2306','2316','2326')", kimlik)
		End Function

		Function MaasAra2(ByVal Token As Token, ByVal kimlik As String) As Recordset
			Return Token.OpenSQL("select * from yaslılık..yasmaasbil where kimlik = @kimlik and hturu in ('2306','2316','2326')", kimlik)
		End Function

		Function Oku(Token As Token, ByVal kimlik As String, ByVal hturu As String) As Recordset
			Return Token.OpenSQL("select * from yaslılık.dbo.kcmaas where kimlik = @kimlik and hturu = @hturu", kimlik, hturu)
		End Function

		Sub KisiKaydet(ByVal Token As Token, ByVal kimlik As String, ByVal hturu As String, ByVal borclu As String, ByVal cektutar As Decimal, ByVal katsayi As Decimal, ByVal donduruldu As String)
			Dim Rs As Recordset = Oku(Token, kimlik, hturu)
			If Rs.EOF Then Rs.AddNew()
			Rs.SetValues("kimlik,hturu,borclu,cektutar,katsayi,islemtar,donduruldu", kimlik, hturu, If(borclu, "1", "0"), cektutar, katsayi, Token.SysTime, If(donduruldu, "1", "0"))
			Rs.Save()
		End Sub

		Function Pusula(ByVal Token As Token, ByVal kimlik As String) As Recordset
			Return Token.OpenSQL("select * from yaslılık..kcmaasdetay where kimlik = @kimlik order by yil,ay", kimlik)
		End Function

		Class Kur

			Shared Function Oku(ByVal Token As Token, ByVal tarih As Date, ByVal parabirimi As String) As Recordset
				Return Token.OpenSQL("select * from yaslılık.dbo.kur where parabirimi = @parabirimi and tarih = @tarih", parabirimi, tarih)
			End Function

			Shared Sub Kaydet(ByVal Token As Token, ByVal tarih As Date, ByVal parabirimi As String, ByVal alis As Decimal)
				Dim Rs As Recordset = Oku(Token, tarih, parabirimi)
				If Rs.EOF Then Rs.AddNew()
				Rs.SetValues("parabirimi,tarih,alis", parabirimi, tarih, alis)
				Rs.Save()
			End Sub

		End Class

		Function BasvuruListesi(ByVal Token As Token) As Recordset
			Return Token.OpenSQL("select * from yaslılık..kcmaas order by kimlik")
		End Function

	End Module
End Namespace
